﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess;
using EntitiesLayer.Entities;
using System.Data;
using System.Data.OracleClient;

namespace DataLayer.DAO
{
   public class UsuarioDAO
    {
        private Connection.Connection cn;
        private OracleConnection objCn;
        
        public UsuarioDAO()
        {
            cn = new Connection.Connection();
            objCn = cn.Conecta();
        }


        public List<Usuario> ValUsuario(string username, string userpass)

            {
            List<Usuario> lstObject = new List<Usuario>();

                try
                {
                    OracleCommand cmd = new OracleCommand("VALIDARUSUARIO", objCn);
                    objCn.Open();
                    cmd.Connection = objCn;
                    cmd.CommandTimeout = 60;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new OracleParameter("RECORDSET", OracleType.Cursor, 100,ParameterDirection.Output,null,DataRowVersion.Current,false,null));
                    cmd.Parameters.Add(new OracleParameter("v_username", OracleType.NVarChar, 10, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, username));
                    cmd.Parameters.Add(new OracleParameter("v_userpass", OracleType.NVarChar, 10, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, userpass));
                    
                    using (IDataReader oRead = cmd.ExecuteReader(CommandBehavior.SingleResult))
                    {
                        if ( oRead != null ){
                            while (oRead.Read())
                            {
                                Usuario oUser = new Usuario();

                                if (!oRead.IsDBNull(oRead.GetOrdinal("IUSU_ID_USUARIO")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetInt32(oRead.GetOrdinal("IUSU_ID_USUARIO")).ToString()))
                                    {
                                        oUser.Id_Usuario = oRead.GetInt32(oRead.GetOrdinal("IUSU_ID_USUARIO"));        
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VUSU_NRO_DOCUMENTO")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VUSU_NRO_DOCUMENTO")).ToString()))
                                    {
                                        oUser.VUSU_NRO_DOCUMENTO = oRead.GetString(oRead.GetOrdinal("VUSU_NRO_DOCUMENTO"));        
                                    }
                                }

                                

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VUSU_NOMBRES")))
                                {
                                    if (!String.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VUSU_NOMBRES"))))
                                    {
                                        oUser.Nombres = oRead.GetString(oRead.GetOrdinal("VUSU_NOMBRES"));
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VUSU_APELLIDOS")))
                                {
                                    if (!String.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VUSU_APELLIDOS"))))
                                    {
                                        oUser.Apellidos = oRead.GetString(oRead.GetOrdinal("VUSU_APELLIDOS"));
                                    }
                                }
                                


                                if (!oRead.IsDBNull(oRead.GetOrdinal("VPER_NOM_PERFIL")))
                                {
                                    if (!String.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VPER_NOM_PERFIL"))))
                                    {
                                        oUser.VPER_NOM_PERFIL = oRead.GetString(oRead.GetOrdinal("VPER_NOM_PERFIL"));
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("IPER_ID_PERFIL")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetInt32(oRead.GetOrdinal("IPER_ID_PERFIL")).ToString()))
                                    {
                                        oUser.IPER_ID_PERFIL = oRead.GetInt32(oRead.GetOrdinal("IPER_ID_PERFIL"));
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VARE_COD_AREA")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VARE_COD_AREA")).ToString()))
                                    {
                                        oUser.VARE_COD_AREA = oRead.GetString(oRead.GetOrdinal("VARE_COD_AREA"));
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VSED_COD_SEDE")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VSED_COD_SEDE")).ToString()))
                                    {
                                        oUser.VSED_COD_SEDE = oRead.GetString(oRead.GetOrdinal("VSED_COD_SEDE"));
                                    }
                                }


                                if (!oRead.IsDBNull(oRead.GetOrdinal("VSED_DESC_SEDE")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VSED_DESC_SEDE")).ToString()))
                                    {
                                        oUser.VSED_DESC_SEDE = oRead.GetString(oRead.GetOrdinal("VSED_DESC_SEDE"));
                                    }
                                }

                                if (!oRead.IsDBNull(oRead.GetOrdinal("VARE_DESC_AREA")))
                                {
                                    if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("VARE_DESC_AREA")).ToString()))
                                    {
                                        oUser.VARE_DESC_AREA = oRead.GetString(oRead.GetOrdinal("VARE_DESC_AREA"));
                                    }
                                }

                                
                                lstObject.Add(oUser);
                                oUser = null;
                               }
                        } 
                    }
                                                                               
                }catch (Exception ex){
                    objCn.Dispose();
                }finally{
                    objCn.Close();
                }

                return lstObject;
           
        }


        



    }
}
